const mysql      = require('mysql');
const fs = require('fs');
const paths=require('path');
const gm = require('gm').subClass({imageMagick: true});
exports.route=function(path,req,res) {
    const next=eval(path);
    next(req,res);
};

const sql = mysql.createConnection({
    host     : 'localhost',
    user     : 'chzh',
    password : 'chzh5188',
    database : 'comic'
});
let list=[];
let top=[];
sql.connect();
const addwork=function(req,res){
    const sq=''
    console.log('INSERT INTO `index`(`works` , `episode` ,`page` ,`url`) , VALUES(\'うどんの国の金色毛鞠 第06巻\',\'第34话\','+(i-131)+', \'p'+i+'.jpg\')')

};
const getpiclist=function(req,res){
    const sq='select * from `index` where `works`=\''+req.body.works+'\' and `episode` = \''+req.body.episode+'\';';
    console.log(sq);
    sql.query(sq,function(err,result,fields){
        if(err) throw err;
        if(result){
            let list=[];
            result.forEach(function(item,i){
                list.push(JSON.parse(JSON.stringify(item).replace('RowDataPacket','')));
            });
            res.json(list);
        }
    })
};
const updatepiclist=function(req,res){
    const sq='update `index` set `'+req.body.role+'`= \''+req.body.username+'\',`'+req.body.role+'_status`='+req.body.status+' where `id`='+req.body.id+';';
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result) res.send("success");
    })
}
const getsubtitlelist=function(req,res){
    const sq='select * from `subtitle` where `workid` = '+req.body.workid;
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result){
            let list=[];
            result.forEach(function(item,i){
                list.push(JSON.parse(JSON.stringify(item).replace('RowDataPacket','')));
            });
            res.json(list);
        }
    })
};
const changesubtitle=function(req,res){
    const sq='update `subtitle` set `text_data`=\''+req.body.text+'\' , `text_moved`= '+ req.body.text_moved+' , `text_x` = '+req.body.text_x+' , `text_y` = '+req.body.text_y+' where `id`='+req.body.id+';';
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result) res.send('success');
    })
};
const addsubtitle=function(req,res){
    const sq='insert `subtitle`(`workid`,`point_start_left`,`point_start_top`,`text_data`,`text_left`) values('+req.body.workid+','+req.body.point_start_left+','+req.body.point_start_top+',\'\','+req.body.text_left+');'
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result) res.send('success');
    })
};
const delsubtitle=function(req,res){
    const sq='delete from `subtitle` where `id`='+ req.body.id +';';
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result) {
            res.send('success');
        }
    })
};
const login=function(req,res){
    const sq='select * from `usertable` where `username`=\''+req.body.username+'\' and `password`=\''+req.body.password+'\';';
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result){
            if(result[0]){
                res.json({
                    login:true,
                    user:{
                        username:result[0].username,
                        power:result[0].power,
                        role:result[0].role
                    },
                })
            }else{
                res.json({
                    login:false
                })
            }
        }
    })
}
const checkuser=function(username,res,callback){
    const sq='select * from `usertable` where `username`=\''+username+'\';';
    console.log(sq);
    sql.query(sq,(err,result)=>{
        if(err) throw err;
        if(result){
            if(result[0]){
                res.json({
                    regist:false,
                    err:"该用户名已存在"
                })
            }else{
                callback();
            }
        }
    })
};
const updateinvite =function(invite){
    const sq='update `userinvite` set `time`=`time`+1 where `invite`=\''+invite+'\';'
    console.log(sq);
    sql.query(sq);
}
const register=function(req,res){
    checkuser(req.body.username,res,()=>{
        const sq='select `power`,`role`,`time`,`limit` from `userinvite` where `invite`=\''+req.body.invite+'\';';

        console.log(sq);
        sql.query(sq,(err,result)=>{
            // if(err) throw err;
            let power='guest';
            let role='reader';
            if(result&&result[0]&&result[0].power){
                power=result[0].power;
                role=result[0].role;
                if(result[0].limit>0&&result[0].time>=result[0].limit){
                    res.json({
                        regist:false,
                        err:"该邀请码已达到上限"
                    });
                    return ;
                }
                updateinvite(req.body.invite);
            }
            const sq='insert `usertable`(`username`,`password`,`power`,`role`) values(\''+req.body.username+'\',\''+req.body.password+'\',\''+power+'\',\''+role+'\');';
            console.log(sq);
            sql.query(sq,(err,result2)=>{
                if(err) throw err;
                if(result2){
                    res.json({
                        regist:true,
                        user:{
                            username:req.body.username,
                            power:power,
                            role:role
                        }
                    })
                }

            })
        })
    })

}


const contenttype={
    'jpg': 'image/jpeg',
    'jpeg': 'image/jpeg',
    'gif': 'image/gif',
    'png': 'image/png'
};
exports.getpic = function(path,param,res) {
    const timestamp=new Date().getTime().toString()+Math.floor(Math.random()*1000);
    const relativepath='public/static/14th/pic'+path;
    const ext=(()=>{
        const temp=path.split('.');
        return temp[temp.length-1];
    })();
    fs.exists(relativepath, function (exists) {
        if(!exists) res.send('file error');

        const ph=gm(relativepath);

        let quality=param.quality||1;
        if(param.height){
            ph.resize(null,param.height*quality)
                .resize(null,param.height)
                .write('public/static/14th/pic/temp/'+timestamp+path.substr(1),function(err){
                    if(err) console.log(err);
                    const pathroot=paths.join(__dirname,'../../../public','static','14th','pic','temp',timestamp+path.substr(1));
                    console.log(pathroot);
                    res.sendFile(pathroot);
                });
        }else{
            res.sendFile(paths.join(__dirname,'../../../public','static','14th','pic','temp',path.substr(1)));
        }

    });
};

global.setInterval(()=>{
    let files = [];
    const url='public/static/14th/pic/temp/';
    //判断给定的路径是否存在
    if( fs.existsSync(url) ) {
        //返回文件和子目录的数组
        files = fs.readdirSync(url);
        files.forEach(function(file,index) {
            const curPath = paths.join(url,file);
            fs.unlinkSync(curPath);
        })
    }
},1000*60*60*1);